

package com.kimo.shop.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.kimo.shop.bean.app.dto.ProductDto;
import com.kimo.shop.bean.app.dto.TagProductDto;
import com.kimo.shop.bean.dto.ProdAndSkuListsDto;
import com.kimo.shop.bean.dto.SearchProdDto;
import com.kimo.shop.bean.enums.FlowLogPageEnum;
import com.kimo.shop.bean.enums.FlowVisitEnum;
import com.kimo.shop.bean.enums.ProdStatusEnums;
import com.kimo.shop.bean.enums.ProdType;
import com.kimo.shop.bean.event.GetTransportNamesEvent;
import com.kimo.shop.bean.event.ProdChangeStatusEvent;
import com.kimo.shop.bean.model.*;
import com.kimo.shop.bean.param.*;
import com.kimo.shop.common.config.Constant;
import com.kimo.shop.common.exception.KimoShopBindException;
import com.kimo.shop.common.i18n.I18nMessage;
import com.kimo.shop.common.i18n.LanguageEnum;
import com.kimo.shop.common.util.*;
import com.kimo.shop.dao.*;
import com.kimo.shop.service.*;
import lombok.AllArgsConstructor;
import ma.glasnost.orika.MapperFacade;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.cache.annotation.Caching;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
@AllArgsConstructor
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements ProductService {

    private final ProductMapper productMapper;
    private final SkuMapper skuMapper;
    private final SkuService skuService;
    private final ProdTagReferenceService prodTagReferenceService;
    private final ProdTagService prodTagService;
    private final CategoryService categoryService;
    private final ApplicationContext applicationContext;
    private final ProdTagReferenceMapper prodTagReferenceMapper;
    private final BasketMapper basketMapper;
    private final ProdLangService prodLangService;
    private final SkuLangMapper skuLangMapper;
    private final OrderMapper orderMapper;
    private final FlowPageAnalysisMapper flowPageAnalysisMapper;
    private final FlowLogService flowLogService;
    private final FlowUserAnalysisMapper flowUserAnalysisMapper;
    private final MapperFacade mapperFacade;
    private final OrderItemMapper orderItemMapper;
    private final OrderRefundMapper orderRefundMapper;
    private final SkuRetailService skuRetailService;
    private final SkuRetailMapper skuRetailMapper;
    private final Snowflake snowflake;
    private final UserCollectionService userCollectionService;

    private static final Logger log = LoggerFactory.getLogger(ProductServiceImpl.class);

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveProduct(Product product, ProductParam productParam) {
        productMapper.insert(product);
        // 保存语言表
        productParam.setProdId(product.getProdId());
        saveLang(productParam);
        if (CollectionUtil.isNotEmpty(product.getSkuList())) {
//            skuMapper.insertBatch(product.getProdId(), product.getSkuList());
            List<Sku> skuList = product.getSkuList();
            Long prodId = product.getProdId();
//            skuList.forEach(sku -> sku.setProdId(product.getProdId()));
            if (Objects.equals(1,productParam.getIsCompose())) {
                for (Sku sku : skuList) {
                    sku.setProdId(prodId);
                    if (StrUtil.isBlank(sku.getModelId())) {
                        sku.setModelId(StringUtils.join("SP",String.valueOf(snowflake.nextId())));
                    }
                }
            } else {
                for (Sku sku : skuList) {
                    sku.setProdId(prodId);
                }
            }
            skuService.insertBatchAndLang(skuList);
            productParam.setSkuList(skuList);
            saveComposeProd(product,productParam);
        }
        if (!Objects.isNull(product.getTagList())){
            prodTagReferenceMapper.insertBatch(product.getShopId(), product.getProdId(), product.getTagList());
        }


    }
    private void saveComposeProd(Product product,ProductParam productParam) {
        Integer isCompose = productParam.getIsCompose();
        if (Objects.isNull(isCompose) || Objects.equals(0,isCompose)) {
            return;
        }
        Long prodId = productParam.getProdId();
        Long shopId = product.getShopId();
        List<Sku> skuList = productParam.getSkuList();
        if(CollectionUtils.isEmpty(skuList)) {
            return;
        }
        List<SkuRetail> skuRetails = new ArrayList<>();
        for (Sku sku : skuList) {
            List<RetailProdParam> skuSingleProds = sku.getSkuSingleProds();
            if (CollectionUtils.isEmpty(skuSingleProds)) {
                continue;
            }
            Long skuId = sku.getSkuId();
            for (RetailProdParam skuSingleProd : skuSingleProds) {
                SkuRetail skuRetail = new SkuRetail();
                skuRetail.setShopId(shopId);
                skuRetail.setProdId(prodId);
                skuRetail.setSkuId(skuId);
                skuRetail.setRetailProdId(skuSingleProd.getRetailProdId());
                skuRetail.setRetailNums(skuSingleProd.getRetailNums());
                skuRetails.add(skuRetail);
            }
        }
        if (CollectionUtils.isEmpty(skuRetails)) {
            return;
        }
        // 批量插入
        skuRetailService.saveBatch(skuRetails);

    }

    @Override
    public IPage<ProductDto> pageByScore(IPage<ProductDto> page) {
        return productMapper.pageByScore(page, I18nMessage.getDbLang());
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    @Caching(evict = {
            @CacheEvict(cacheNames = "product", key = "#product.prodId"),
            @CacheEvict(cacheNames = "skuList", key = "#product.prodId")
    })
    public void updateProduct(ProductParam product, Product dbProduct) {
        // 使用保存或更新，可以仅更新非null的数据
        Product productDb = mapperFacade.map(product, Product.class);
        productDb.setDeliveryMode(Json.toJsonString(product.getDeliveryModeVo()));
        productDb.setUpdateTime(new Date());
        saveOrUpdate(productDb);
        // 删除商品对应语言信息列表
        prodLangService.remove(new LambdaQueryWrapper<ProdLang>().eq(ProdLang::getProdId, product.getProdId()));
        // 保存语言表信息
        saveLang(product);
        List<Long> dbSkuIds = dbProduct.getSkuList().stream().map(Sku::getSkuId).collect(Collectors.toList());
        // 2019/04/c0244be79909484fb67bc6d5f70cae18.jpg
//        if (!Objects.equals(dbProduct.getPic(), product.getPic()) && StrUtil.isNotBlank(dbProduct.getPic())) {
//            // 删除数据库中的商品图片
//            attachFileService.deleteFile(dbProduct.getPic());
//        }
        // 将所有该商品的sku标记为已删除状态
        skuMapper.deleteByProdId(product.getProdId());

        // 接口传入sku列表
        List<Sku> skuList = product.getSkuList();

        if (CollectionUtil.isEmpty(skuList)) {
            return;
        }

        List<Sku> insertSkuList = new ArrayList<>();
        for (Sku sku : skuList) {
            sku.setIsDelete(0);
            // 如果数据库中原有sku就更新，否者就插入
            if (dbSkuIds.contains(sku.getSkuId())) {
                skuService.updateSku(sku);
            } else {
                if (dbProduct.getGroupActivityId() != null && dbProduct.getGroupActivityId() != 0) {
                    // 商品正在参与拼团活动，无法修改规格
                    throw new KimoShopBindException("kimo.product.join.group");
                }
                if (dbProduct.getSeckillActivityId() != null && dbProduct.getSeckillActivityId() != 0) {
                    // 商品正在参与秒杀活动，无法修改规格
                    throw new KimoShopBindException("kimo.product.join.seckill");
                }
                insertSkuList.add(sku);
            }
        }
        // 批量插入sku
        if (CollectionUtil.isNotEmpty(insertSkuList)) {
            for (Sku sku : skuList) {
                sku.setProdId(product.getProdId());
            }
            skuService.insertBatchAndLang(insertSkuList);
        }

        // 需要删除的skuIds对应的购物车
        deleteShopCart(dbSkuIds, skuList);

        //更新分组信息
        List<Long> tagList = product.getTagList();
        if (CollectionUtil.isNotEmpty(tagList)) {
            prodTagReferenceMapper.delete(new LambdaQueryWrapper<ProdTagReference>().eq(ProdTagReference::getProdId, product.getProdId()));
            prodTagReferenceMapper.insertBatch(dbProduct.getShopId(), product.getProdId(), tagList);
        }
        updateComposeProd(dbProduct,product,insertSkuList);
        // 如果修改成预售商品，则删除用户的购物车中的当前商品
        if(Objects.nonNull(product.getPreSellStatus()) && Objects.equals(product.getPreSellStatus(),1)) {
            basketMapper.delete(new LambdaQueryWrapper<Basket>().eq(Basket::getProdId, product.getProdId()));
        }
    }

    /**
     * 删除sku时，删除掉关联购物车
     * @param dbSkuIds 数据库sku信息
     * @param skuList 此时修改后的信息
     */
    private void deleteShopCart(List<Long> dbSkuIds, List<Sku> skuList) {
        List<Long> deleteSkuIds = new ArrayList<>();
        List<Long> existSkuId = skuList.stream().map(Sku::getSkuId).collect(Collectors.toList());
        for (Long dbSkuId : dbSkuIds) {
            if (!existSkuId.contains(dbSkuId)) {
                deleteSkuIds.add(dbSkuId);
            }
        }
        // 如果不为空，则进行删除关联的购物车
        if(CollectionUtils.isNotEmpty(deleteSkuIds)){
            for (Long deleteSkuId : deleteSkuIds) {
                basketMapper.delete(new LambdaQueryWrapper<Basket>().eq(Basket::getSkuId,deleteSkuId));
            }
        }
    }


    /**
     * 更新组合商品
     * 新增的sku 没有skuId，需要插入后返回skuid,所以需要 插入sku的集合
     * @param product
     * @param productParam
     * @param insertSkuList 新增的sku
     */
    private void updateComposeProd(Product product,ProductParam productParam, List<Sku> insertSkuList) {
        Integer isCompose = productParam.getIsCompose();
        if (Objects.isNull(isCompose) || Objects.equals(0,isCompose)) {
            return;
        }
        Long shopId = product.getShopId();
        Long prodId = productParam.getProdId();
        // 更新的sku 单品
        List<Sku> skuList = productParam.getSkuList();
        // 去除新增的部分，新增的sku单独处理，insertSkuList是新增的部分
        skuList = skuList.stream().filter(item->Objects.nonNull(item.getSkuId())).collect(Collectors.toList());
        // 将该商品修改之前的所有sku与单品的关联关系全部查询出来
        List<SkuRetail> oriList = skuRetailService.list(new LambdaQueryWrapper<SkuRetail>()
                .eq(SkuRetail::getProdId, prodId)
                .eq(SkuRetail::getStatus, 1));
        Map<Long, List<SkuRetail>> listMap = oriList.stream().collect(Collectors.groupingBy(SkuRetail::getSkuId));

        List<Long> skuIds = skuList.stream().map(Sku::getSkuId).collect(Collectors.toList());
        Map<Long, Sku> skus = skuList.stream().collect(Collectors.toMap(Sku::getSkuId,(k1)->k1));
        // 新增的sku
        Set<SkuRetail> insertSkuRetails = new HashSet<SkuRetail>();
        // 删除skuId,的同时解除与单品的关联关系
        Set<Long> removeSkuRetailId = new HashSet<Long>();

        // 需要修改的sku 组合内单品数量的
        Set<SkuRetail> updateSkuRetail = new HashSet<SkuRetail>();
        insertSkuList.forEach(item -> {
            Long skuId = item.getSkuId();
            List<RetailProdParam> skuSingleProds = item.getSkuSingleProds();
            if(skuSingleProds!=null) {
                for (RetailProdParam skuSingleProd : skuSingleProds) {
                    SkuRetail skuRetail = new SkuRetail();
                    skuRetail.setSkuId(skuId);
                    skuRetail.setRetailProdId(skuSingleProd.getRetailProdId());
                    skuRetail.setShopId(shopId);
                    skuRetail.setProdId(prodId);
                    skuRetail.setRetailNums(skuSingleProd.getRetailNums());
                    skuRetail.setStatus(1);
                    insertSkuRetails.add(skuRetail);
                }
            }
        });
        listMap.forEach((skuId,lists)->{
            if (skuIds.contains(skuId)) {
                // 接口传过来的商品的单个sku中，新增，修改，删除组合内的单品
                // 更新策略，这个没有唯一key,但是可以通过skuid 和 单品的id 去唯一确认，
                // 因为一个sku里面的单品不会重复
                Sku skuItem = skus.get(skuId);
                // 原来的每个sku中的单品
                List<Long> oriSkuRetailIds = lists.stream().map(SkuRetail::getRetailProdId).collect(Collectors.toList());

                List<RetailProdParam> skuSingleProds = skuItem.getSkuSingleProds();
                List<Long> newSkuRetailIds = skuSingleProds.stream().map(RetailProdParam::getRetailProdId).collect(Collectors.toList());

                for (SkuRetail retail : lists) {
                    // 参数中的单品不包括原来的关联单品，则是需要删除的单品
                    if (!newSkuRetailIds.contains(retail.getRetailProdId())) {
                        removeSkuRetailId.add(retail.getSkuRetailId());
                        continue;
                    }
                    for (RetailProdParam singleProd : skuSingleProds) {
                        // 新增的单品在原来的关联单品列表不存在，则是需要新增的数据
                        if (!oriSkuRetailIds.contains(singleProd.getRetailProdId())) {
                            SkuRetail skuRetail = new SkuRetail();
                            skuRetail.setSkuId(skuId);
                            skuRetail.setStatus(1);
                            skuRetail.setShopId(shopId);
                            skuRetail.setProdId(prodId);
                            skuRetail.setRetailProdId(singleProd.getRetailProdId());
                            skuRetail.setRetailNums(singleProd.getRetailNums());
                            insertSkuRetails.add(skuRetail);
                            continue;
                        }
                        if (Objects.equals(retail.getRetailProdId(),singleProd.getRetailProdId())) {
                            if (Objects.equals(retail.getRetailNums(),singleProd.getRetailNums())){
                                continue;
                            } else {
                                retail.setRetailNums(singleProd.getRetailNums());
                                updateSkuRetail.add(retail);
                            }
                        }
                    }
                }
            } else {
                // 如果原来的sku，在修改中没有，则需要被删除
                lists.forEach(k->{
                    removeSkuRetailId.add(k.getSkuRetailId());
                });
            }
        });

        if (CollectionUtils.isNotEmpty(insertSkuRetails)) {
            // 批量插入
            skuRetailService.saveBatch(new ArrayList<SkuRetail>(insertSkuRetails));
        }
        // 删除的sku部分，对应的sku与单品的关系也删除
        if (CollectionUtils.isNotEmpty(removeSkuRetailId)) {
            skuRetailMapper.removeBatchByIds(new ArrayList<Long>(removeSkuRetailId));
        }
        // 更新sku的单品部分，更新组合内的单品数量，新增一项sku的单品
        if (CollectionUtils.isNotEmpty(updateSkuRetail)) {
            skuRetailMapper.updateBatchBySkuId(new ArrayList<SkuRetail>(updateSkuRetail));
        }
    }

    private void saveLang(ProductParam productParam) {
        List<ProdLang> prodLangs = new ArrayList<>();
        ProdLang prodLang = new ProdLang();
        prodLang.setLang(LanguageEnum.LANGUAGE_EN.getLang());
        prodLang.setProdName(productParam.getProdNameEn());
        prodLang.setBrief(productParam.getBriefEn());
        prodLang.setContent(productParam.getContentEn());
        prodLang.setProdId(productParam.getProdId());
        prodLangs.add(prodLang);

        ProdLang prodLangCn = new ProdLang();
        prodLangCn.setLang(LanguageEnum.LANGUAGE_ZH_CN.getLang());
        prodLangCn.setProdName(productParam.getProdNameCn());
        prodLangCn.setBrief(productParam.getBriefCn());
        prodLangCn.setContent(productParam.getContentCn());
        prodLangCn.setProdId(productParam.getProdId());
        prodLangs.add(prodLangCn);
        prodLangService.saveBatch(prodLangs);
    }
    /**
     * 这里的缓存 1800秒消失，详情RedisCacheConfig这里的配置
     * 当秒杀的时候，并不更新这里的缓存，所以库存会滞后，但是半个小时后缓存消失，又会重新获取
     *
     * @param prodId
     * @param dbLang
     * @return
     */
    @Override
    @Cacheable(cacheNames = "product", key = "#prodId+'-'+#dbLang")
    public Product getProductByProdId(Long prodId, Integer dbLang) {
        return productMapper.selectByIdAndLang(prodId,dbLang);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    @Caching(evict = {
            @CacheEvict(cacheNames = "product", key = "#prodId"),
            @CacheEvict(cacheNames = "skuList", key = "#prodId")
    })
    public void removeProductByProdId(Long prodId) {
        Product dbProduct = getProductByProdId(prodId,I18nMessage.getDbLang());
        if (Objects.equals(1,dbProduct.getIsCompose())) {
            // 如果是组合商品就，解除关联单品
            skuRetailMapper.deleteByProdId(dbProduct.getProdId());
        }
        // 将商品的状态变成-1（逻辑删除）
        Product product = new Product();
        product.setProdId(prodId);
        product.setStatus(-1);
        saveOrUpdate(product);

        skuMapper.deleteByProdId(prodId);

        //删除商品关联的分组标签
        prodTagReferenceMapper.delete(new LambdaQueryWrapper<ProdTagReference>()
                .eq(ProdTagReference::getProdId, prodId));
        //删除用户收藏中的该商品
        userCollectionService.remove(new LambdaQueryWrapper<UserCollection>()
                .eq(UserCollection::getProdId,prodId));
        // 删除语言表信息
        prodLangService.remove(new LambdaUpdateWrapper<ProdLang>().eq(ProdLang::getProdId,prodId));
        skuLangMapper.deleteByProdId(prodId);
        // 商品状态改变时的发送事件，让活动下线
        dbProduct.setStatus(-1);
        applicationContext.publishEvent(new ProdChangeStatusEvent(dbProduct,dbProduct.getStatus()));
        // 删除数据库中的商品图片
//        if (StrUtil.isNotBlank(dbProduct.getImgs())) {
//            String[] imgs = dbProduct.getImgs().split(StrUtil.COMMA);
//            for (String img : imgs) {
//                attachFileService.deleteFile(img);
//            }
//        }
    }

    @Override
    @Caching(evict = {
            @CacheEvict(cacheNames = "product", key = "#prodId"),
            @CacheEvict(cacheNames = "skuList", key = "#prodId")
    })
    public void removeProductCacheByProdId(Long prodId) {


    }

    @Override
    public void updateWaterSoldNum(Integer waterSoldNum,Long prodId) {
        productMapper.updateWaterSoldNum(waterSoldNum,prodId);
    }

    @Override
    public IPage<ProductDto> pageByPutawayTime(IPage<ProductDto> page, Integer dbLang) {
        return productMapper.pageByPutawayTime(page,dbLang);
    }

    @Override
    public IPage<ProductDto> pageByTagId(Page<ProductDto> page, Long tagId, Integer dbLang) {
        return productMapper.pageByTagId(page, tagId,dbLang);
    }

    @Override
    public IPage<ProductDto> moreBuyProdList(Page<ProductDto> page, Integer dbLang) {
        return productMapper.moreBuyProdList(page,dbLang);
    }

    @Override
    public IPage<ProductDto> pageByCategoryId(Page<ProductDto> page, Long categoryId) {
        return productMapper.pageByCategoryId(page, categoryId);
    }

    @Override
    public IPage<SearchProdDto> getSearchProdDtoPageByProdName(Page page, SearchParam searchParam) {
        IPage<SearchProdDto> searchProdDtoPage = productMapper.getSearchProdDtoPageByProdName(page, searchParam);
        for (SearchProdDto searchProdDto : searchProdDtoPage.getRecords()) {
            //计算出好评率
            if (searchProdDto.getPraiseNumber() == 0 || searchProdDto.getProdCommNumber() == 0) {
                searchProdDto.setPositiveRating(0.0);
            } else {
                searchProdDto.setPositiveRating(Arith.div(Arith.mul(searchProdDto.getPraiseNumber(), 100), searchProdDto.getProdCommNumber(),2));
            }
        }
        return searchProdDtoPage;
    }

    @Override
    public IPage<SearchProdDto> getSearchCategory(Page page, SearchParam searchParam) {
        IPage<SearchProdDto> searchProdDtoPage = productMapper.getSearchCategory(page, searchParam);
        for (SearchProdDto searchProdDto : searchProdDtoPage.getRecords()) {
            //计算出好评率
            if (searchProdDto.getPraiseNumber() == 0 || searchProdDto.getProdCommNumber() == 0) {
                searchProdDto.setPositiveRating(0.0);
            } else {
                searchProdDto.setPositiveRating(Arith.div(Arith.mul(searchProdDto.getPraiseNumber(), 100), searchProdDto.getProdCommNumber(),2));
            }
        }
        return searchProdDtoPage;
    }

    @Override
    public List<TagProductDto> tagProdList() {
        return productMapper.tagProdList();
    }

    @Override
    public IPage<ProductDto> pageByDiscountId(Page<ProductDto> page, Long discountId, Integer dbLang) {
        return productMapper.pageByDiscountId(page, discountId,dbLang);
    }

    @Override
    public IPage<ProductDto> collectionProds(PageParam page, String userId, Integer dbLang) {
        return productMapper.collectionProds(page, userId,dbLang);
    }

    @Override
    public List<ProdAndSkuListsDto> getProdAndSkuLists(List<Long> prodIds) {
        return productMapper.getProdAndSkuLists(prodIds);
    }

    @Override
    public void updateProductToGroup(Long groupActivityId, List<Long> prodIds) {
        productMapper.updateProductToGroup(groupActivityId, prodIds);
    }

    @Override
    public void updateProductToUnGroup(Long prodId) {
        productMapper.updateProductToUnGroup(prodId);
    }

    @Override
    public ProdAnalysisParam getProdSurvey(ProdAnalysisSurveyParam param) {
        flowLogService.insertBatch();
        ProdAnalysisParam analysisParam = new ProdAnalysisParam();
        ProdAnalysisDataParam data = new ProdAnalysisDataParam();
        ProdAnalysisDataParam lastData = new ProdAnalysisDataParam();
        ProdAnalysisRateParam rate = new ProdAnalysisRateParam();
        Integer dateType = param.getDateType();
        Date startTime = param.getStartTime();
        Date endTime = param.getEndTime();
        param.setDateTime(endTime);
        data.setCurrentDay(DateUtils.dateToStrYMD(param.getDateTime()));
        // 在架商品数据
        data.setOnSale(productMapper.countProdOnSale(param));
        param.setDateTime(null);
        // 统计时间内销量不为零的商品数量
        data.setDynamicSale(productMapper.countProdDynamicSale(param));
        // 统计时间内，成功下单的商品件数之和（不剔除退款订单）
        data.setOrderNum(setDefaultZero(orderMapper.countOrderNum(param)));
        // 统计时间内， 成功付款订单的商品件数之和（不剔除退款订单）
        data.setPayNum(setDefaultZero(orderMapper.countPayNum(param)));
        if (Objects.equals(1,dateType)) {
            // 今日实时
            param.setDateTime(null);
            // 被访问商品数 统计时间内，商品详情页浏览次数大于 0 的商品数
            data.setVisitedProd(setDefaultZero(flowLogService.countVisitProdNum(param)));
            // 商品浏览量
            data.setBrowse(setDefaultZero(flowLogService.countVisitProdInfoNum(param)));
            // 商品曝光数
            data.setExpose(setDefaultZero(flowLogService.countProdExposeNum(param)));
            // 商品访客数
            data.setVisitor(setDefaultZero(flowLogService.countProdVisitorNum(param)));
            // 统计时间内，添加商品进入购物车的商品件数
            data.setAddCart(setDefaultZero(flowLogService.countAddCartProdNum(param)));
            // 统计时间内，分享访问次数
            data.setShareVisit(setDefaultZero(flowLogService.countShareVisit(param)));
        }else {
            param.setDateTime(null);
            // 被访问商品数 统计时间内，商品详情页浏览次数大于 0 的商品数
            data.setVisitedProd(setDefaultZero(flowPageAnalysisMapper.countVisitProdNum(param)));
            // 商品浏览量
            data.setBrowse(setDefaultZero(flowPageAnalysisMapper.countVisitProdInfoNum(param)));
            // 商品曝光数
            data.setExpose(setDefaultZero(flowPageAnalysisMapper.countProdExposeNum(param)));
            // 商品访客数
            data.setVisitor(setDefaultZero(flowPageAnalysisMapper.countProdVisitorNum(param)));
            // 统计时间内，添加商品进入购物车的商品件数
            data.setAddCart(setDefaultZero(flowPageAnalysisMapper.countAddCartProdNum(param)));
            // 统计时间内，分享访问商品数
            data.setShareVisit(setDefaultZero(flowPageAnalysisMapper.countShareVisitNum(param)));
        }
        if (!Objects.equals(1,dateType)) {
            // 统计时间前的数据-----------------------------------------------------------------------------------
            // 前多少天的数据
            Integer days = Integer.valueOf(String.valueOf(DateUtil.betweenDay(startTime, endTime, true)));
            param.setDateTime(DateUtils.getBeforeDay(startTime,-days));
            lastData.setOnSale(productMapper.countProdOnSale(param));
            param.setDateTime(null);
            param.setStartTime(DateUtils.getBeforeDay(startTime,-days));
            param.setEndTime(DateUtil.endOfDay(DateUtils.getBeforeDay(startTime,-1)));
            lastData.setPayNum(setDefaultZero(orderMapper.countPayNum(param)));
            lastData.setCurrentDay(DateUtils.dateToStrYMD(startTime));
            lastData.setExpose(setDefaultZero(flowPageAnalysisMapper.countProdExposeNum(param)));
            lastData.setDynamicSale(productMapper.countProdDynamicSale(param));
            lastData.setOrderNum(setDefaultZero(orderMapper.countOrderNum(param)));
            lastData.setVisitedProd(setDefaultZero(flowPageAnalysisMapper.countVisitProdNum(param)));
            lastData.setBrowse(setDefaultZero(flowPageAnalysisMapper.countVisitProdInfoNum(param)));
            lastData.setAddCart(setDefaultZero(flowPageAnalysisMapper.countAddCartProdNum(param)));
            lastData.setShareVisit(setDefaultZero(flowPageAnalysisMapper.countShareVisitNum(param)));
            // 数据比例-----------------------------------------------------------------------------------
            rate.setOnSaleRate(divAverage(Arith.sub(data.getOnSale(), lastData.getOnSale()), lastData.getOnSale(), 4));
            rate.setDynamicSaleRate(divAverage(Arith.sub(data.getDynamicSale(), lastData.getDynamicSale()), lastData.getDynamicSale(), 4));
            rate.setOrderNumRate(divAverage(Arith.sub(data.getOrderNum(), lastData.getOrderNum()), lastData.getOrderNum(), 4));
            rate.setPayNumRate(divAverage(Arith.sub(data.getPayNum(), lastData.getPayNum()), lastData.getPayNum(), 4));
            rate.setVisitedProdRate(divAverage(Arith.sub(data.getVisitedProd(),lastData.getVisitedProd()),lastData.getVisitedProd(),4));
            rate.setBrowseRate(divAverage(Arith.sub(data.getBrowse(),lastData.getBrowse()),lastData.getBrowse(),4));
            rate.setExposeRate(divAverage(Arith.sub(data.getExpose(),lastData.getExpose()),lastData.getExpose(),4));
            rate.setVisitorRate(divAverage(Arith.sub(data.getVisitor(),lastData.getVisitor()),lastData.getVisitor(),4));
            rate.setAddCartRate(divAverage(Arith.sub(data.getAddCart(),lastData.getAddCart()),lastData.getAddCart(),4));
            rate.setShareVisitRate(divAverage(Arith.sub(data.getShareVisit(),lastData.getShareVisit()),lastData.getShareVisit(),4));
        }
        // 数据回填-----------------------------------------------------------------------------------
        analysisParam.setData(data);
        analysisParam.setLastData(lastData);
        analysisParam.setRate(rate);
        return analysisParam;
    }

    @Override
    public List<ProdAnalysisDataParam> getProdTrendAnalysis(ProdAnalysisSurveyParam param) {
        List<ProdAnalysisDataParam> datas = new ArrayList<>();
        Date startTime = param.getStartTime();
        Date endTime = param.getEndTime();
        // 商品访客数列表
        List<ProdAnalysisDataParam> prodVisitorList = flowUserAnalysisMapper.countProdVisitorNum(param);
        Map<String, ProdAnalysisDataParam> prodVisitorMap = prodVisitorList.stream().collect(Collectors.toMap(ProdAnalysisDataParam::getCurrentDay, p -> p));
        //数据统计列表
        List<ProdAnalysisDataParam> flowUserAnalysisList = flowUserAnalysisMapper.analysisSurvey(param);
        Map<String, ProdAnalysisDataParam> flowUserAnalysisMap = flowUserAnalysisList.stream().collect(Collectors.toMap(ProdAnalysisDataParam::getCurrentDay, p -> p));
        //订单统计列表
        List<ProdAnalysisDataParam> prodSurveyList = orderMapper.prodSurveyList(param);
        Map<String, ProdAnalysisDataParam> prodSurveyMap = prodSurveyList.stream().collect(Collectors.toMap(ProdAnalysisDataParam::getCurrentDay, p -> p));
        while(startTime.getTime() <= endTime.getTime()){
            String date = DateUtils.dateToStrYMD(startTime);
            ProdAnalysisDataParam prodAnalysisDataParam = new ProdAnalysisDataParam();
            prodAnalysisDataParam.setCurrentDay(date);
            if(prodVisitorMap.containsKey(date)){
                prodAnalysisDataParam.setVisitor(prodVisitorMap.get(date).getVisitor());
            }
            if(flowUserAnalysisMap.containsKey(date)){
                prodAnalysisDataParam.setVisitedProd(flowUserAnalysisMap.get(date).getVisitedProd());
                prodAnalysisDataParam.setAddCart(flowUserAnalysisMap.get(date).getAddCart());
                prodAnalysisDataParam.setBrowse(flowUserAnalysisMap.get(date).getBrowse());
                prodAnalysisDataParam.setShareVisit(flowUserAnalysisMap.get(date).getShareVisit());
            }
            if(prodSurveyMap.containsKey(date)){
                prodAnalysisDataParam.setDynamicSale(prodSurveyMap.get(date).getDynamicSale());
                prodAnalysisDataParam.setPayNum(prodSurveyMap.get(date).getPayNum());
                prodAnalysisDataParam.setOrderNum(prodSurveyMap.get(date).getOrderNum());
            }
            startTime = DateUtil.offsetDay(startTime,1);
            param.setDateTime(endTime);
            // 在架商品数据
            prodAnalysisDataParam.setOnSale(productMapper.countProdOnSale(param));
            datas.add(prodAnalysisDataParam);
        }
        return datas;
    }

    @Override
    public IPage<ProdEffectRespParam> getProdEffect(PageParam<Product> page,ProdEffectParam param) {
        // 查询出在架商品
        Integer status = param.getStatus();
        if (Objects.isNull(status)) {
            param.setStatus(0);
        }
        Date startTime = param.getStartTime();
        Date endTime = param.getEndTime();
        Set<Long> prodIdSet = getFlowProdId(param.getDateType(), startTime, endTime);
        IPage<ProdEffectRespParam> pages = productMapper.getProdByParam(page,param, prodIdSet);
        param.setStatus(status);

        List<Long> prodIds = pages.getRecords().stream().map(ProdEffectRespParam::getProdId).collect(Collectors.toList());
        if (CollUtil.isEmpty(prodIds)) {
            return pages;
        }

        List<ProdEffectRespParam> prodFlowList = getProdFlowData(param.getDateType(), prodIds, startTime, endTime);

        List<ProdEffectRespParam> prodOrderList = orderItemMapper.getProdEffectByDateAndProdIds(prodIds, startTime, endTime);

        List<ProdEffectRespParam> prodOrderRefundList = orderRefundMapper.getProdEffectByDateAndProdIds(prodIds, startTime, endTime);

        Map<Long, ProdEffectRespParam> prodFlowMap = prodFlowList.stream().collect(Collectors.toMap(ProdEffectRespParam::getProdId, p -> p));
        Map<Long, ProdEffectRespParam> prodOrderMap = prodOrderList.stream().collect(Collectors.toMap(ProdEffectRespParam::getProdId, p -> p));
        Map<Long, ProdEffectRespParam> prodOrderRefundMap = prodOrderRefundList.stream().collect(Collectors.toMap(ProdEffectRespParam::getProdId, p -> p));
        for (ProdEffectRespParam respParam : pages.getRecords()) {
            // 商品相关数据
            ProdEffectRespParam prodFlowParam = prodFlowMap.get(respParam.getProdId());
            if (Objects.nonNull(prodFlowParam)) {
                // 加购件数
                respParam.setAddCart(prodFlowParam.getAddCart());
                // 加购人数
                respParam.setAddCartPerson(prodFlowParam.getAddCartPerson());
                // 曝光人数
                respParam.setExposePersonNum(prodFlowParam.getExposePersonNum());
                // 曝光次数
                respParam.setExpose(prodFlowParam.getExpose());
            }

            //订单相关
            ProdEffectRespParam prodOrderParam = prodOrderMap.get(respParam.getProdId());
            if (Objects.nonNull(prodOrderParam)) {
                // 商品下单人数
                respParam.setPlaceOrderPerson(prodOrderParam.getPlaceOrderPerson());
                // 支付人数
                respParam.setPayPerson(prodOrderParam.getPayPerson());
                // 单品转化率 = 商品付款人数/商品访客人数
                respParam.setSingleProdRate(divAverage(respParam.getPayPerson(),respParam.getExposePersonNum(),4));
                // 商品下单数
                respParam.setPlaceOrderNum(prodOrderParam.getPlaceOrderNum());
                // 支付商品件数
                respParam.setPayNum(prodOrderParam.getPayNum());
                // 商品下单金额
                respParam.setPlaceOrderAmount(prodOrderParam.getPlaceOrderAmount());
                // 商品支付金额
                respParam.setPayAmount(setDefaultDouble(prodOrderParam.getPayAmount()));
            }

            //退款相关
            ProdEffectRespParam prodOrderRefundParam = prodOrderRefundMap.get(respParam.getProdId());
            if (Objects.nonNull(prodOrderRefundParam)) {
                // 申请退款订单数
                respParam.setRefundNum(prodOrderRefundParam.getRefundNum());
                // 申请退款人数
                respParam.setRefundPerson(prodOrderRefundParam.getRefundPerson());
                // 成功退款订单数
                respParam.setRefundSuccessNum(prodOrderRefundParam.getRefundSuccessNum());
                // 成功退款人数
                respParam.setRefundSuccessPerson(prodOrderRefundParam.getRefundSuccessPerson());
                // 成功退款金额
                respParam.setRefundSuccessAmount(prodOrderRefundParam.getRefundSuccessAmount());
                // 退款率 = 成功退款订单数/申请退款订单数
                respParam.setRefundSuccessRate(divAverage(respParam.getRefundSuccessNum(),respParam.getRefundNum(),4));
            }
        }
        return pages;
    }

    private Set<Long> getFlowProdId(Integer dateType, Date startTime, Date endTime) {
        Set<Long> prodIdSet = new HashSet<>();
        if (Objects.equals(1, dateType)) {
            List<FlowLog> flowLogList = flowLogService.getFlowLogListByCache();
            List<Long> prodIds = flowLogList.stream()
                    .filter(flowLog -> FlowLogPageEnum.isProdInfo(flowLog.getPageId()) && StrUtil.isNotBlank(flowLog.getBizData()))
                    .map(flowLog -> Long.valueOf(flowLog.getBizData())).collect(Collectors.toList());
            prodIdSet.addAll(prodIds);
        } else {
            List<String> prodIds = flowPageAnalysisMapper.getProdIdsByTime(startTime, endTime);
            Pattern pattern = Pattern.compile("[0-9]+");
            for (String prodId : prodIds) {
                if (pattern.matcher(prodId).matches()) {
                    prodIdSet.add(Long.valueOf(prodId));
                }
            }
        }
        if(CollUtil.isEmpty(prodIdSet)) {
            return null;
        }
        return prodIdSet;
    }

    private List<ProdEffectRespParam> getProdFlowData(Integer dateType, List<Long> prodIds, Date startTime, Date endTime) {
        List<ProdEffectRespParam> prodFlowList = new ArrayList<>();
        // 加购件数、加购人数、曝光人数、曝光次数、访客数
        if (Objects.equals(1, dateType)) {
            List<FlowLog> flowLogList = flowLogService.getFlowLogListByCache();
            Map<String, List<FlowLog>> flowLogMap = flowLogList.stream()
                    .filter(flowLog ->
                            (FlowLogPageEnum.isProdInfo(flowLog.getPageId()) || Objects.equals(flowLog.getVisitType(),FlowVisitEnum.SHOP_CAT.value()))
                            && Objects.nonNull(flowLog.getBizData())
                            && prodIds.contains(Long.valueOf(flowLog.getBizData())))
                    .collect(Collectors.groupingBy(FlowLog::getBizData));
            for (String key : flowLogMap.keySet()) {
                List<FlowLog> flowLogs = flowLogMap.get(key);
                ProdEffectRespParam prodEffectRespParam = new ProdEffectRespParam();
                int addCart = 0;
                Set<String> addCartPerson = new HashSet<>();
                int expose = 0;
                Set<String> exposePersonNum = new HashSet<>();
                for (FlowLog flowLog : flowLogs) {
                    if (Objects.equals(flowLog.getVisitType(), FlowVisitEnum.SHOP_CAT.value())) {
                        addCart = addCart + flowLog.getNums();
                        addCartPerson.add(flowLog.getUserId());
                    }else if (FlowVisitEnum.isVisitOrShare(flowLog.getVisitType())) {
                        expose++;
                        if (Objects.nonNull(flowLog.getUserId())) {
                            exposePersonNum.add(flowLog.getUserId());
                        } else {
                            exposePersonNum.add(flowLog.getUuid());
                        }
                    }
                }
                prodEffectRespParam.setProdId(Long.valueOf(key));
                prodEffectRespParam.setAddCart(addCart);
                // 加购人数
                prodEffectRespParam.setAddCartPerson(addCartPerson.size());
                // 曝光人数
                prodEffectRespParam.setExposePersonNum(exposePersonNum.size());
                // 曝光次数
                prodEffectRespParam.setExpose(expose);

                prodFlowList.add(prodEffectRespParam);
            }
        } else {
            prodFlowList = flowPageAnalysisMapper.getProdEffectByDateAndProdIds(prodIds, startTime, endTime);
        }
        return prodFlowList;
    }

    @Override
    public List<ProdSingleTrendParam> getSingleProdTrend(Long prodId, ProdEffectParam param) {
        Date startTime = param.getStartTime();
        Date endTime = param.getEndTime();
        List<DateParam> dates = DateUtils.findEveryDays(startTime, endTime);
        List<ProdSingleTrendParam> resList = new ArrayList<>();
        for (DateParam date : dates) {
            ProdSingleTrendParam res = new ProdSingleTrendParam();
            res.setCurrentDay(DateUtils.dateToStrYMD(date.getStartTime()));
            param.setStartTime(date.getStartTime());
            param.setEndTime(date.getEndTime());
            // 浏览量
            res.setBrowse(setDefaultZero(flowPageAnalysisMapper.countProdExposeNumByParam(prodId,param.getStartTime(),param.getEndTime())));
            // 访客数
            res.setVisitor(setDefaultZero(flowPageAnalysisMapper.countProdVisitorNumByProdId(prodId,param.getStartTime(),param.getEndTime())));
            // 付款人数
            res.setPayPerson(orderItemMapper.countPayPerson(prodId,param));
            // 付款商品件数
            res.setPayNum(setDefaultZero(productMapper.countPayNum(prodId, param)));
            resList.add(res);
        }
        return resList;
    }

    @Override
    @Caching(evict = {
            @CacheEvict(cacheNames = "product", key = "#prodId+'-'+#lang"),
            @CacheEvict(cacheNames = "skuList", key = "#prodId")
    })
    public void removeProdCacheByProdId(Long prodId, Integer lang) {

    }

    @Override
    public IPage<Product> pageByLang(PageParam<Product> page, ProductParam product) {
        Integer dbLang = I18nMessage.getDbLang();
        IPage<Product> productPage = productMapper.pageByLang(page, product);
        for (Product prod : productPage.getRecords()) {
            Product.DeliveryModeVO deliveryModeVO = Json.parseObject(prod.getDeliveryMode(), Product.DeliveryModeVO.class);
            StringBuilder deliveryMode = new StringBuilder();
            if(deliveryModeVO != null) {
                if (deliveryModeVO.getHasCityDelivery() != null && deliveryModeVO.getHasCityDelivery()) {
                    deliveryMode.append(Objects.equals(LanguageEnum.LANGUAGE_EN.getLang(), dbLang) ? "Same CityDelivery, " : "同城配送，");
                }
                if (deliveryModeVO.getHasUserPickUp() != null && deliveryModeVO.getHasUserPickUp()) {
                    deliveryMode.append(Objects.equals(LanguageEnum.LANGUAGE_EN.getLang(), dbLang) ? "User PickUp, " : "用户自提，");
                }
                if (deliveryModeVO.getHasShopDelivery() != null && deliveryModeVO.getHasShopDelivery()) {
                    deliveryMode.append(Objects.equals(LanguageEnum.LANGUAGE_EN.getLang(), dbLang) ? "Merchant Mailing, " : "商家邮寄，");
                }
                deliveryMode.deleteCharAt(deliveryMode.length() - 1);
            }
            prod.setDeliveryMode(deliveryMode.toString());
        }
        return productPage;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public  List<Product> recoveryPreSaleProd() {
        List<Product> products = list(new LambdaUpdateWrapper<Product>().eq(Product::getPreSellStatus, 1).lt(Product::getPreSellTime, new Date()));
        if(CollectionUtils.isEmpty(products)){
            return null;
        }
        productMapper.updatePreSale(products);
        return products;
    }

    @Override
    public List<Product> getProductListBySpuIds(Set<Long> prodIds) {
        if (CollectionUtil.isEmpty(prodIds)) {
            return new ArrayList<>();
        }
        return productMapper.getProductListBySpuIds(prodIds);
    }


    @Override
    public void exportProd(ProductExportParam productParam, HttpServletResponse response, Long shopId) {
        List<ProductExportParam> products = productMapper.listProdsByProdParam(productParam);
        createExcel(products,response,shopId);
    }

    private void createExcel(List<ProductExportParam> products, HttpServletResponse response,Long shopId) {

        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 商品导出or模板
        List<String> headerList;
        if(CollectionUtils.isNotEmpty(products)) {
            String[] header = {"产品中文名称", "产品英文名称", "分类选择", "产品中文卖点", "产品英文卖点", "配送方式", "运费选择", "产品状态",
                    "规格名称", "商品中文名称", "商品英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格",
                    "库存", "商家编码", "商品重量", "商品体积"};
            headerList = Arrays.asList(header);
        }else{
            String[] header = {"产品中文名称（必填*）", "产品英文名称",  "分类选择（必填*）", "产品中文卖点（必填*）", "产品英文卖点", "配送方式", "运费选择（必填*）",
                    "产品状态", "规格名称", "商品名称", "商品英文名称","销售属性组合字符串 格式是p1:v1;p2:v2",
                    "原价", "价格", "库存", "商家编码", "商品重量", "商品体积"};
            headerList = Arrays.asList(header);
        }
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 60 * 256);
        sheet.setColumnWidth(1, 60 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(7, 60 * 256);
        sheet.setColumnWidth(8, 60 * 256);
        sheet.setColumnWidth(9, 60 * 256);

        writer.merge(headerList.size() - 1, "商品信息整理");
        writer.writeRow(headerList);
        // 获取平台分类map
        List<Category> categories = categoryService.list(new LambdaQueryWrapper<Category>()
                .eq(Category::getStatus,ProdStatusEnums.NORMAL.getValue()).eq(Category::getShopId,1).eq(Category::getGrade,2));
        if(CollectionUtils.isNotEmpty(categories)) {
            String[] categoryName = new String[categories.size()];
            for (int i = 0; i < categories.size(); i++) {
                categoryName[i] = categories.get(i).getCategoryName();
            }
            createDropDownList(sheet, categoryName, 2, 50000, 2, 2);
        }
        // 限制物流方式
        String[] dvyTypes = {"用户自提","同城配送","用户自提且同城配送"};
        createDropDownList(sheet, dvyTypes, 2, 50000, 5, 5);
//        // 限制品牌选择
//        List<Brand> brands = brandService.list(new LambdaQueryWrapper<Brand>().eq(Brand::getStatus, 1));
//        if(CollectionUtils.isNotEmpty(brands)) {
//            String[] categoryName = new String[brands.size()];
//            for (int i = 0; i < brands.size(); i++) {
//                categoryName[i] = brands.get(i).getBrandName();
//            }
//            createDropDownList(sheet, categoryName, 2, 50000, 3, 3);
//        }
        List<TransportParam> transportParams = new ArrayList<>();
        applicationContext.publishEvent(new GetTransportNamesEvent(transportParams,shopId));
        if(CollectionUtils.isNotEmpty(transportParams)) {
            List<String> transportNameList = new ArrayList<>();
            transportParams.forEach(transportParam -> transportNameList.add(transportParam.getTransName()));
            String[] transportNames = transportNameList.toArray(new String[transportNameList.size()]);
            createDropDownList(sheet,transportNames,2,50000,6,6);
        }
        String[] statusStr = {"下架","上架"};
        createDropDownList(sheet,statusStr,2,50000,7,7);
        int row = 1;
        if(CollectionUtils.isNotEmpty(products)) {
            for (ProductExportParam prod : products) {
                List<Sku> skuList = prod.getSkuList();
                int firstRow = row + 1;
                int lastRow = row + skuList.size();
                int col = -1;
                String prodNameEn = "";
                String briefEn = "";
                String prodNameCn = "";
                String briefCn = "";
                // 获取中文、英文信息
                List<ProdLang> prodLangList = prod.getProdLangList();
                for (ProdLang prodLang : prodLangList) {
                    if(Objects.equals(prodLang.getLang(),LanguageEnum.LANGUAGE_EN.getLang())){
                        prodNameEn = prodLang.getProdName();
                        briefEn = prodLang.getBrief();
                    }else{
                        prodNameCn = prodLang.getProdName();
                        briefCn = prodLang.getBrief();
                    }
                }

                // 产品中文名称
//                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getPic());
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodNameCn);
                // 产品英文名称
//                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getPic());
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodNameEn);
                // 平台分类
                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getCategoryName());
//                // "所属品牌"
//                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getBrandName());
                // "产品卖点"
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, briefCn);
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, briefEn);
                Product.DeliveryModeVO deliveryModeVO = Json.parseObject(prod.getDeliveryMode(), Product.DeliveryModeVO.class);
                StringBuilder deliveryMode = new StringBuilder();
                if(deliveryModeVO != null) {
                    if (deliveryModeVO.getHasCityDelivery() != null && deliveryModeVO.getHasCityDelivery()) {
                        deliveryMode.append("同城配送，");
                    }
                    if (deliveryModeVO.getHasUserPickUp() != null && deliveryModeVO.getHasUserPickUp()) {
                        deliveryMode.append("用户自提，");
                    }
                    if (deliveryModeVO.getHasShopDelivery() != null && deliveryModeVO.getHasShopDelivery()) {
                        deliveryMode.append("商家邮寄，");
                    }
                    deliveryMode.deleteCharAt(deliveryMode.length() - 1);
                }
                //TODO 配送方式
                mergeIfNeed(writer, firstRow, lastRow, ++col, col, deliveryMode);
                // 运费设置
                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getTransName());
                // 产品状态
                mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getStatus() == 1 ? "上架" : "下架");
                for (Sku sku : skuList) {
                    int index = col;
                    row++;
                    // 规格名称
                    writer.writeCellValue(++index, row, sku.getSkuName());
                    // 商品中文名称
                    writer.writeCellValue(++index, row, sku.getProdNameCn());
                    // 商品英文名称
                    writer.writeCellValue(++index, row, sku.getProdNameEn());
                    // 属性名称
                    writer.writeCellValue(++index, row, sku.getProperties());
                    // 规格图片
//                    writer.writeCellValue(++index, row, sku.getPic());
                    // 原价
                    writer.writeCellValue(++index, row, sku.getOriPrice());
                    // 实际价格
                    writer.writeCellValue(++index, row, sku.getPrice());
                    // 积分价格
//                    writer.writeCellValue(++index, row, sku.getSkuScore());
                    // 库存
                    writer.writeCellValue(++index, row, sku.getStocks());
                    // 商家编码
                    writer.writeCellValue(++index, row, sku.getPartyCode());
//                    // 商家条形码
//                    writer.writeCellValue(++index, row, sku.getModelId());
                    // 商品重量
                    writer.writeCellValue(++index, row, sku.getWeight());
                    // 商品体积
                    writer.writeCellValue(++index, row, sku.getVolume());
                }
            }
        }
        PoiExcelUtil.writeExcel(response, writer);
    }

    @Override
    public void downloadModel(HttpServletResponse response, Long shopId) {
        createExcel(null,response,shopId);
    }

    @Override
    public Object parseFile(MultipartFile file, Long shopId,boolean isSameCity) throws Exception {
        try(InputStream inputStream = file.getInputStream();
            Workbook workBook = WorkbookFactory.create(inputStream)) {
            //读取工作表
            Sheet sheet = workBook.getSheetAt(0);
            int rowNumber = sheet.getPhysicalNumberOfRows();

            //校验是否填写内容
            if (rowNumber <= 2) {
                // 文件无内容
                throw new KimoShopBindException("kimo.document.no.content");
            }
            // * 2.1不是Excel文件，直接返回提示信息
            if (!(file.getOriginalFilename().contains("xls") || file.getOriginalFilename().contains("xlsx"))) {
                // 格式错误
                // 文件格式错误
                throw  new KimoShopBindException("kimo.file.error");
            }
            // 获取平台分类map
            List<Category> categories = categoryService.list(new LambdaQueryWrapper<Category>()
                    .eq(Category::getStatus,ProdStatusEnums.NORMAL.getValue()).eq(Category::getShopId,1).eq(Category::getGrade,2));
            // 如果重复的key取最后一个的值
            Map<String, Long> categoryMap = categories.stream().collect(Collectors.toMap(Category::getCategoryName, Category::getCategoryId,(key1 , key2)-> key2));
            // 获取运费模板map
            List<TransportParam> transportParams = new ArrayList<>();
            applicationContext.publishEvent(new GetTransportNamesEvent(transportParams,shopId));
            Map<String, Long> transportMap = transportParams.stream().collect(Collectors.toMap(TransportParam::getTransName, TransportParam::getTransportId,(key1 , key2)-> key2));

            List<Product> products = new ArrayList<>();
            List<Sku> skuList = new ArrayList<>();
            Date date = new Date();
            Integer totalStocks = 0;
            double price = 0.0;
            double oriPrice = 0.0;
            Integer scorePrice = 0;
            int insertRows = 0;
            Product product = new Product();
            List<ProdLang> prodLangs;
            String response;
            List<String> errorList = new ArrayList<>();
            //循环读取每一行数据并校验
            Row firstRow = sheet.getRow(1);
            for (int i = 2; i < rowNumber; i++) {
                try {
                    //读取行
                    Row row = sheet.getRow(i);
                    this.isAllRowEmpty(row, firstRow);
                    int cell = 0;
                    String prodName = PoiExcelUtil.getRowValue(row,cell,false);
                    String prodNameEn = PoiExcelUtil.getRowValue(row,++cell,false);
                    String category = PoiExcelUtil.getRowValue(row,++cell,false);
                    String brief = PoiExcelUtil.getRowValue(row,++cell,false);
                    String briefEn = PoiExcelUtil.getRowValue(row,++cell,false);
                    String dvyType = PoiExcelUtil.getRowValue(row,++cell,false);
                    String transportName = PoiExcelUtil.getRowValue(row,++cell,false);
                    String statusStr = PoiExcelUtil.getRowValue(row,++cell,false);
                    if(StrUtil.isNotBlank(prodName) || !PoiExcelUtil.isMergedRegion(sheet,i,0)) {
                        if (StrUtil.isBlank(prodName)) {
                            // 产品中文名称不能为空
                            throw new KimoShopBindException("kimo.prod.name.not.blank");
                        }
                        if (Objects.isNull(prodNameEn)) {
                            prodNameEn = prodName;
                        }
                        if (StrUtil.isBlank(category)) {
                            // 产品分类不能为空
                            throw new KimoShopBindException("kimo.prod.category.not.blank");
                        }
                        if (StrUtil.isBlank(brief)) {
                            // 产品中文卖点不能为空
                            throw new KimoShopBindException("kimo.prod.brief.not.blank");
                        }
                        if (StrUtil.isBlank(briefEn)) {
                            briefEn = brief;
                        }
                        if (Objects.isNull(transportMap.get(transportName))) {
                            // 产品运费模板不存在
                            throw new KimoShopBindException("kimo.prod.transport.not.exist");
                        }

                        skuList = new ArrayList<>();
                        product = new Product();
                        prodLangs = new ArrayList<>();

                        // 保存语言信息
                        ProdLang prodLang = new ProdLang();
                        prodLang.setBrief(brief);
                        prodLang.setLang(LanguageEnum.LANGUAGE_ZH_CN.getLang());
                        prodLang.setProdName(prodName);
                        ProdLang prodLangEn = new ProdLang();
                        prodLangEn.setBrief(briefEn);
                        prodLangEn.setLang(LanguageEnum.LANGUAGE_EN.getLang());
                        prodLangEn.setProdName(prodNameEn);
                        prodLangs.add(prodLang);
                        prodLangs.add(prodLangEn);
                        product.setProdLangList(prodLangs);
                        totalStocks = 0;
                        oriPrice = 0.0;
                        scorePrice = 0;
                        price = 0.0;
                        insertRows++;
                        product.setShopId(shopId);
                        product.setProdName(prodName);
                        product.setCategoryId(categoryMap.get(category));
                        product.setBrief(brief);
                        product.setDeliveryMode(getDvyType(dvyType,isSameCity));
                        product.setDeliveryTemplateId(transportMap.get(transportName));
                        //TODO 差商品的图片
                        product.setStatus(Objects.equals(statusStr, "上架") ? ProdStatusEnums.NORMAL.getValue() : ProdStatusEnums.SHOP_OFFLINE.getValue());
                        product.setPutawayTime(Objects.equals(statusStr, "上架") ? date : null);
                        product.setCreateTime(date);
                        product.setUpdateTime(date);
                        product.setVersion(0);
                        // 商品销量设置为0
                        product.setSoldNum(0);
                        product.setPic("2020/07/f29c335ff19746cda1025f363dcc00cf.png");
                        product.setImgs("2020/07/f29c335ff19746cda1025f363dcc00cf.png");
                        product.setProdType(ProdType.PROD_TYPE_NORMAL.value());
                        product.setProdType(0);
                        product.setGroupActivityId(0L);
                        product.setSeckillActivityId(0L);
                        product.setContent("<p></p>");
                        products.add(product);
                    }
//                  String[] hearder = { "产品名称", "平台分类", "产品卖点", "配送方式","运费设置","产品状态",
//                        "规格名称","商品名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "积分价格",
//                        "库存","商家编码","商品条形码" ,"商品重量", "商品体积"};
                    String  skuName = PoiExcelUtil.getRowValue(row,++cell,false);
                    String skuProdNameCn = PoiExcelUtil.getRowValue(row,++cell,false);
                    String skuProdNameEn = PoiExcelUtil.getRowValue(row,++cell,false);
                    String  properties = PoiExcelUtil.getRowValue(row,++cell,false);
                    double skuOriPrice = Double.valueOf(PoiExcelUtil.getRowValue(row,++cell,true));
                    if(Objects.isNull(skuOriPrice) || skuOriPrice < Constant.MIN_PRODUCT_AMOUNT) {
                        skuOriPrice = Constant.MIN_PRODUCT_AMOUNT;
                    }
                    double skuPrice = Double.valueOf(PoiExcelUtil.getRowValue(row,++cell,true));
                    if(Objects.isNull(skuPrice) || skuPrice < Constant.MIN_PRODUCT_AMOUNT) {
                        skuPrice = Constant.MIN_PRODUCT_AMOUNT;
                    }
                    Integer stocks = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell,true)).intValue();
                    String partyCode = "";
                    if(Objects.nonNull(row.getCell(cell))) {
                        partyCode = PoiExcelUtil.getRowValue(row,++cell,false);
                    }
                    double weight = Double.valueOf(PoiExcelUtil.getRowValue(row,++cell,true));
                    double volume = Double.valueOf(PoiExcelUtil.getRowValue(row,++cell,true));
                    oriPrice = Objects.equals(oriPrice ,0.0) ? skuOriPrice:oriPrice;
                    price = Objects.equals(price ,0.0) ? skuPrice:price;

                    Sku sku = getSku(date, skuName, skuProdNameCn, skuProdNameEn, properties, skuOriPrice, skuPrice, stocks, partyCode, weight, volume);
                    skuList.add(sku);
                    // 如果prodName为空并且为合并行则为一个prod
                    totalStocks += stocks;
                    if(price > skuPrice) {
                        oriPrice = skuOriPrice;
                        price = skuPrice;
                    }
                    product.setOriPrice(oriPrice);
                    product.setPrice(price);
                    product.setTotalStocks(totalStocks);
                    product.setSkuList(skuList);
                } catch (Exception e) {
                    //错误信息提示
                    errorList.add("第" + (i + 1) + "行数据有错误：" + e.getMessage());
                }
            }
            //不为空批量导入
            if(CollectionUtils.isNotEmpty(products)){
                int errorCount = 0;
                int count = 0;
                saveBatch(products);
                // 同时保存最初的商品分组
                ProdTag prodTag = prodTagService.getOne(new LambdaQueryWrapper<ProdTag>().eq(ProdTag::getStatus, 1).orderByAsc(ProdTag::getCreateTime).last("limit 1"));
                List<ProdTagReference> prodTags= new ArrayList<>();
                for (Product productDb : products) {
                    count++;
                    try {
                        List<Sku> skus = productDb.getSkuList();
//                    skuMapper.insertBatch(productDb.getProdId(),skus);
                        skus.forEach(sku -> sku.setProdId(productDb.getProdId()));
                        skuService.insertBatchAndLang(skus);
                        // 保存语言表信息
                        List<ProdLang> prodLangList = productDb.getProdLangList();
                        prodLangList.forEach(prodLang -> prodLang.setProdId(productDb.getProdId()));
                        prodLangService.saveBatch(prodLangList);

                        // 同时保存最初的商品分组
                        ProdTagReference prodTagReference = getProdTagReference(date, prodTag, productDb);
                        prodTags.add(prodTagReference);
                    }catch (Exception e) {
                        //错误信息提示
                        errorCount++;
                        errorList.add("第" + count + "行数据有错误:" + e.getMessage());
                        if(productDb.getProdId() != null) {
                            removeProductByProdId(productDb.getProdId());
                        }
                    }
                }
                prodTagReferenceService.saveBatch(prodTags);
                response = "检查到" + insertRows + "条商品，成功导入" +
                        (products.size()-errorCount) + "条商品信息！错误" + errorCount + "条商品信息！" +
                        (CollUtil.isNotEmpty(errorList)?errorList.toString():"");
                return response;
            }
            response = "数据错误!导入0条";
            if (CollUtil.isNotEmpty(errorList)) {
                response = response + errorList.toString();
            }
            return response;
        } catch (Exception e) {
            return e.getMessage();
        }
    }

    private ProdTagReference getProdTagReference(Date date, ProdTag prodTag, Product productDb) {
        ProdTagReference prodTagReference = new ProdTagReference();
        prodTagReference.setProdId(productDb.getProdId());
        prodTagReference.setStatus(1);
        prodTagReference.setShopId(Constant.PLATFORM_SHOP_ID);
        prodTagReference.setTagId(prodTag.getId());
        prodTagReference.setCreateTime(date);
        return prodTagReference;
    }

    private Sku getSku(Date date, String skuName, String skuProdNameCn, String skuProdNameEn, String properties, double skuOriPrice, double skuPrice, Integer stocks, String partyCode, double weight, double volume) {
        Sku sku = new Sku();
        sku.setSkuName(skuName);
        sku.setProdName(skuProdNameCn);
        sku.setProdNameCn(skuProdNameCn);
        sku.setProdNameEn(skuProdNameEn);
        sku.setProperties(properties);
        sku.setOriPrice(skuOriPrice);
        sku.setPrice(skuPrice);
        sku.setStocks(stocks);
//                sku.setSkuScore(skuScorePrice);
//                sku.setModelId(modelId);
        sku.setPartyCode(partyCode);
        sku.setVolume(volume);
        sku.setUpdateTime(date);
        sku.setWeight(weight);
        sku.setVersion(1);
        sku.setIsDelete(0);
        sku.setStatus(1);
        return sku;
    }

    /**
     * 验证excel是否全部为空
     * @param row 当前行
     * @param firstRow 第一行标题行
     * @return
     */
    private boolean isAllRowEmpty(Row row,Row firstRow) {
        int count = 0;
        //单元格数量
        int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
        //判断多少个单元格为空
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null || cell.getCellType() == CellType.STRING || this.isEmpty((cell+"").trim())){
                count += 1;
            }
        }
        if (count == rowCount) {
            return true;
        }
        return false;
    }

    /**
     * 检测字符串是否为空(null,"","null")
     * @param s
     * @return 为空则返回true，不否则返回false
     */
    private boolean isEmpty(String s){
        return s==null || "".equals(s) || "null".equals(s);
    }

    private String getDvyType(String dvyType,boolean isSameCity) {
        Product.DeliveryModeVO mode = new Product.DeliveryModeVO();
        mode.setHasShopDelivery(true);
        mode.setHasUserPickUp(false);
        mode.setHasCityDelivery(false);
        if (Objects.isNull(dvyType)) {
            return Json.toJsonString(mode);
        }
        String[] split = dvyType.trim().split(StrUtil.COMMA);
        for (String str : split) {
            if(StrUtil.equals(str,"用户自提")){
                mode.setHasUserPickUp(true);
            }
            if(StrUtil.equals(str,"同城配送")){
                mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
            }
            if(StrUtil.equals(str,"用户自提且同城配送")){
                mode.setHasUserPickUp(true);
                mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
            }
        }
        return Json.toJsonString(mode);
    }

    /**
     * 创建下拉列表选项
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    private void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    /**
     * 如果需要合并的话，就合并
     */
    private void mergeIfNeed(ExcelWriter writer, int firstRow, int lastRow, int firstColumn, int lastColumn, Object content) {
        if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
            writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
        } else {
            writer.writeCellValue(firstColumn, firstRow, content);
        }

    }

    public Integer  setDefaultZero(Integer data) {
        if (Objects.isNull(data)) {
            return 0;
        }
        return data;
    }
    public Double  setDefaultDouble(Double data) {
        if (Objects.isNull(data)) {
            return 0.0;
        }
        return data;
    }

    private Double divAverage(Integer a, Integer b, Integer scale) {
        if (Objects.isNull(b) || b == 0 || Objects.isNull(a)) {
            return 0.0;
        }
        return Arith.div(a, b, scale);
    }

    private Double divAverage(Double a, Integer b, Integer scale) {
        if (Objects.isNull(b) || b == 0 || Objects.isNull(a)) {
            return 0.0;
        }
        return Arith.div(a, b, scale);
    }
    private Double divAverage(Double a, Double b, Integer scale) {
        if (Objects.isNull(b) || b == 0 || Objects.isNull(a)) {
            return 0.0;
        }
        return Arith.div(a, b, scale);
    }

}
